package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.Movie;
import bg.cinema_system.pojo.Projection;
import bg.cinema_system.pojo.User;

public class UserDAO {

	public static User isUserExist(String username, String password) throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		User user = new User();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement = conn.prepareStatement("SELECT * FROM users WHERE username = ? AND password = md5(?) ");
			prepareStatement.setString(1, username);
			prepareStatement.setString(2, password);
			
			rs = prepareStatement.executeQuery();
			rs.first();
			user.setUserId(rs.getInt("user_id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(password);
			user.setEmail(rs.getString("email"));
			user.setRole(rs.getInt("fk_role_id"));
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}

		return user;
	}

	public static boolean createNewUser(User user) throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		boolean isUserExist = true;
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement = conn.prepareStatement("SELECT username FROM Users WHERE username = ?");
			prepareStatement.setString(1, user.getUsername());
			rs = prepareStatement.executeQuery();
			isUserExist = rs.first();
			prepareStatement.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new SQLException(e);
			}
		}

		// If user doesn't exist then insert new one
		if (!isUserExist) {
			PreparedStatement prepareStatementInsert = null;
			try {
				prepareStatementInsert = conn
						.prepareStatement("INSERT INTO Users (username, password, email, fk_role_id) VALUES ( ?, md5(?), ?, 1)");
				prepareStatementInsert.setString(1, user.getUsername());
				prepareStatementInsert.setString(2, user.getPassword());
				prepareStatementInsert.setString(3, user.getEmail());
				
				// Execute insert SQL statement
				prepareStatementInsert.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new SQLException(e);
			} finally {
				try {
					// Close all connections
					prepareStatementInsert.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new SQLException(e);
				}
			}
		}

		conn.close();
		return isUserExist;
	}

	public static boolean checkPassword(int userId, String password) throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		boolean checkResult = false;
		try {
			prepareStatement = conn
					.prepareStatement("SELECT users.password FROM users WHERE user_id = ? AND password = md5(?)");
			prepareStatement.setInt(1, userId);
			prepareStatement.setString(2, password);
			
			// Execute SELECT SQL statement
			rs = prepareStatement.executeQuery();
			checkResult = rs.first();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new SQLException(e);
			}
		}

		return checkResult;
	}

	public static void changePassword(int userId, String password) throws SQLException{
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = conn
					.prepareStatement("UPDATE Users SET password = md5(?) WHERE user_id = ?");
			prepareStatement.setString(1, password);
			prepareStatement.setInt(2, userId);
			
			// Execute update SQL statement
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new SQLException(e);
			}
		}
	}
	
	public static String getUserMail(int userId) throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = null;
		String mail = null;
		ResultSet rs = null;
		try {
			prepareStatement = conn.prepareStatement("SELECT email FROM users WHERE user_id = ?");
			prepareStatement.setInt(1, userId);
			
			rs = prepareStatement.executeQuery();
			rs.first();
			mail = rs.getString("email");
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}

		return mail;
	}
}